package com.mdsi.teacher;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mdsi.models.Absence;
import com.mdsi.models.ConnectionManager;
import com.mdsi.models.Department;
import com.mdsi.models.Lesson;
import com.mdsi.models.Room;
import com.mdsi.models.Student;
import com.mdsi.models.Subject;
import com.mdsi.models.Teacher;

/**
 * Servlet implementation class CheckAbsence
 */
@WebServlet("/User/Teacher/CheckAbsence")
public class CheckAbsence extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public CheckAbsence() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		if (request.getParameter("lesson") == null) {
			response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
			return ;
		}
		try {
			Lesson lesson = null ;
			Teacher teacher = (Teacher) request.getSession().getAttribute("user") ;
			Connection conn = ConnectionManager.getConnection() ;
			PreparedStatement st = null ;
			ResultSet result = null ;
			st = conn.prepareStatement("SELECT DISTINCT L.SUBJECT.ID_SUBJECT, L.SUBJECT.NAME, "
					+ "L.ID_LESSON, L.START_HOUR, L.END_HOUR, "
					+ "L.ROOM.ID_ROOM, L.ROOM.NUM, L.ROOM.TYPE_ROOM, L.ROOM.DEPT.ID_DEPARTMENT, L.ROOM.DEPT.NAME, L.TEACHER.ID_TEACHER "
					+ "FROM LESSON L "
					+ "WHERE L.ID_LESSON = ?") ;			
			st.setLong(1, Long.valueOf(request.getParameter("lesson"))) ;
			result = st.executeQuery() ;
			if (!result.next()) {
				conn.close() ;
				response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
				return ;
			}
			Calendar start = GregorianCalendar.getInstance() ;
			start.setTime(result.getDate("START_HOUR")) ;
			Calendar end = GregorianCalendar.getInstance() ;
			end.setTime(result.getDate("END_HOUR")) ;
			lesson = new Lesson(
					result.getLong("ID_LESSON"),
					new Subject(result.getLong("SUBJECT.ID_SUBJECT"), 
							result.getString("SUBJECT.NAME")),
					new Room(result.getLong("ROOM.ID_ROOM"),
							Room.TypeRoom.valueOf(result.getString("ROOM.TYPE_ROOM")),
							new Department(result.getLong("ROOM.DEPT.ID_DEPARTMENT"), 
									result.getString("ROOM.DEPT.NAME")),
							result.getInt("ROOM.NUM")
							), 
					start, end, 
					teacher
					) ;
			if (!teacher.getId().equals(result.getLong("TEACHER.ID_TEACHER"))) {
				response.sendRedirect(request.getContextPath() + "/User/Unauthorized") ;
				return ;
			}
			st = conn.prepareStatement("SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
							+ "FROM STUDENT S, REGISTER R "
							+ "WHERE R.SUBJECT.ID_SUBJECT = " + lesson.getSubject().getId() + " "
							+ "ORDER BY S.FIRSTNAME, S.LASTNAME") ;
			result = st.executeQuery() ;
			ArrayList <Student> students = new ArrayList <Student> () ;
			while (result.next()) {
				Calendar birthdate = GregorianCalendar.getInstance() ;
				birthdate.setTime(result.getDate("BIRTHDATE")) ;
				Student student = new Student(
					result.getLong("ID_STUDENT"), 
					result.getString("FIRSTNAME"), 
					result.getString("LASTNAME"),
					result.getString("EMAIL"), 
					result.getLong("NUM"), 
					birthdate, 
					result.getString("NATIONALITY")) ;
				students.add(student) ;
				ResultSet absence = conn
						.prepareStatement("SELECT UNIQUE A.ID_ABSENCE, A.JUSTIFY, A.JUSTIFICATION, A.LEVELJUSTIFICATION "
								+ "FROM ABSENCE A "
								+ "WHERE A.LESSON.ID_LESSON = " + lesson.getId() + " "
								+ "AND A.STUDENT.ID_STUDENT = " + student.getId()).executeQuery() ;
				if (absence.next()) {
					new Absence(absence.getLong("ID_ABSENCE"), 
							lesson, student, 
							"Y".equals(absence.getString("JUSTIFY")), 
							absence.getString("JUSTIFICATION"), 
							absence.getInt("LEVELJUSTIFICATION")) ;
				}
			}
			
			conn.close () ;
			getServletContext().setAttribute("lesson", lesson) ;
			getServletContext().setAttribute("students", students) ;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		this.getServletContext().getRequestDispatcher("/WEB-INF/teacher/check_absence.jsp").forward(request, response);			
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		if (request.getParameter("lesson") == null) {
			response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
			return ;	
		}
		try {
			Lesson lesson = null ;
			Teacher teacher = (Teacher) request.getSession().getAttribute("user") ;
			Connection conn = ConnectionManager.getConnection() ;
			PreparedStatement st = null ;
			ResultSet result = null ;
			st = conn.prepareStatement("SELECT DISTINCT L.SUBJECT.ID_SUBJECT, L.SUBJECT.NAME, "
					+ "L.ID_LESSON, L.START_HOUR, L.END_HOUR, "
					+ "L.ROOM.ID_ROOM, L.ROOM.NUM, L.ROOM.TYPE_ROOM, L.ROOM.DEPT.ID_DEPARTMENT, L.ROOM.DEPT.NAME, L.TEACHER.ID_TEACHER "
					+ "FROM LESSON L "
					+ "WHERE L.ID_LESSON = ?") ;			
			st.setLong(1, Long.valueOf(request.getParameter("lesson"))) ;
			result = st.executeQuery() ;
			if (!result.next()) {
				conn.close() ;
				response.sendRedirect(request.getContextPath() + "/User/Teacher/SubjectList") ;
				return ;
			}
			Calendar start = GregorianCalendar.getInstance() ;
			start.setTime(result.getDate("START_HOUR")) ;
			Calendar end = GregorianCalendar.getInstance() ;
			end.setTime(result.getDate("END_HOUR")) ;
			lesson = new Lesson(
					result.getLong("ID_LESSON"),
					new Subject(result.getLong("SUBJECT.ID_SUBJECT"), 
							result.getString("SUBJECT.NAME")),
					new Room(result.getLong("ROOM.ID_ROOM"),
							Room.TypeRoom.valueOf(result.getString("ROOM.TYPE_ROOM")),
							new Department(result.getLong("ROOM.DEPT.ID_DEPARTMENT"), 
									result.getString("ROOM.DEPT.NAME")),
							result.getInt("ROOM.NUM")
							), 
					start, end, 
					teacher
					) ;
			if (!teacher.getId().equals(result.getLong("TEACHER.ID_TEACHER"))) {
				response.sendRedirect(request.getContextPath() + "/User/Unauthorized") ;
				return ;
			}
			st = conn.prepareStatement("SELECT DISTINCT S.ID_STUDENT, S.NUM, S.FIRSTNAME, S.LASTNAME, S.EMAIL, S.BIRTHDATE, S.NATIONALITY "
							+ "FROM STUDENT S, REGISTER R "
							+ "WHERE R.SUBJECT.ID_SUBJECT = " + lesson.getSubject().getId() + " "
							+ "ORDER BY S.FIRSTNAME, S.LASTNAME") ;
			result = st.executeQuery() ;
			ArrayList <Student> students = new ArrayList <Student> () ;
			while (result.next()) {
				Calendar birthdate = GregorianCalendar.getInstance() ;
				birthdate.setTime(result.getDate("BIRTHDATE")) ;
				Student student = new Student(
					result.getLong("ID_STUDENT"), 
					result.getString("FIRSTNAME"), 
					result.getString("LASTNAME"),
					result.getString("EMAIL"), 
					result.getLong("NUM"), 
					birthdate, 
					result.getString("NATIONALITY")) ;
				students.add(student) ;
				conn.prepareStatement("DELETE FROM ABSENCE A "
						+ "WHERE A.LESSON.ID_LESSON = " + lesson.getId() + " "
						+ "AND A.STUDENT.ID_STUDENT = " + student.getId()).execute() ;
				String where = "WHERE A.LESSON.ID_LESSON = " + lesson.getId() + " AND A.STUDENT.ID_STUDENT = " + student.getId() ;
				if (request.getParameter("student_" + student.getId()) == null) {
					if (!conn.prepareStatement("SELECT * FROM ABSENCE A " + where).executeQuery() .next()) {
						conn.prepareStatement("INSERT INTO ABSENCE (LESSON, STUDENT, JUSTIFY, JUSTIFICATION, LEVELJUSTIFICATION) VALUES "
								+ "((SELECT REF(L) FROM LESSON L WHERE L.ID_LESSON = " + lesson.getId() + "), "
								+ "(SELECT REF(S) FROM STUDENT S WHERE S.ID_STUDENT = " + student.getId() + "), "
								+ "'N', '', 0)").execute() ;
					}
				}
				else {
					conn.prepareStatement("DELETE FROM ABSENCE A "
							+ where + student.getId()).execute() ;
				}
			}
			conn.close () ;
			response.sendRedirect(request.getContextPath() + "/User/Teacher/LessonForSubject?subject=" + lesson.getSubject().getId()) ;
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
